﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data.Linq;
using DataObject.Database;
using DataObject.Core;
using DataObject.Helpers;
using DataObject.Model;

namespace DataObject.DataAccess
{
    public class ProviderDAO : DataBaseProcessBase<ProviderEntity, OBSDataContext>
    {
        #region Singleton
        public static ProviderDAO CreateInstant()
        {
            return new ProviderDAO();
        } 
        #endregion

        public IList<ProviderEntity> GetProviders(
            string providerNumber, string providerName, string email, string website,
            string addressLine, string city, string telephone, string fax,
            int? Page, int? Row, out int Count, HashSet<string> SortClause)
        {
            using (OBSDataContext db = new OBSDataContext(base.connectionString))
            {
                db.DeferredLoadingEnabled = false; //disabling will turn off deferred loading (lazy fetch)

                var query = (from x in db.ProviderEntities where x.Active select x);
                if (!String.IsNullOrEmpty(providerNumber))
                    query = query.Where(x => x.ProviderNumber.ToLower().Equals(providerNumber.ToLower()));
                if (!String.IsNullOrEmpty(providerName))
                    query = query.Where(x => x.ProviderName.ToLower().Contains(providerName.ToLower()));
                if (!String.IsNullOrEmpty(email))
                    query = query.Where(x => x.Email.ToLower().Contains(email.ToLower()));
                if (!String.IsNullOrEmpty(website))
                    query = query.Where(x => x.Website.ToLower().Contains(website.ToLower()));
                if (!String.IsNullOrEmpty(addressLine))
                    query = query.Where(x => String.Concat(x.AddressLine1.ToLower(), x.AddressLine2.ToLower()).Contains(addressLine.ToLower()));
                if (!String.IsNullOrEmpty(city))
                    query = query.Where(x => x.City.ToLower().Contains(city.ToLower()));
                if (!String.IsNullOrEmpty(telephone))
                    query = query.Where(x => x.Phone.ToLower().Contains(telephone.ToLower()));
                if (!String.IsNullOrEmpty(fax))
                    query = query.Where(x => x.FaxNumber.ToLower().Contains(fax.ToLower()));
                for (int i = SortClause.Count - 1; i >= 0; i--)
                    query = query.OrderBy(SortClause.ElementAt(i));
                Count = query.Count();
                query = query.OrderByDescending(b => b.ModifiedDate);
                if (Page.HasValue && Row.HasValue)
                    return query.PageIQuery(Page.Value, Row.Value).ToList();
                return query.ToList();
            }
        }

        public bool DeleteProvider(Guid providerID)
        {
            bool result = false;
            try
            {
                using (OBSDataContext db = new OBSDataContext(base.connectionString))
                {
                    var provider = db.ProviderEntities.Where(x => x.ProviderID == providerID).SingleOrDefault();
                    if (provider != null)
                    {
                        if (provider.ReservationServices.Count > 0 ||
                            provider.RouteServices.Count > 0)
                            provider.Active = false;
                        else
                        {
                            db.ServiceProviders.DeleteAllOnSubmit(provider.ServiceProviders);
                            db.ProviderContacts.DeleteAllOnSubmit(provider.ProviderContacts);
                            db.ProviderEntities.DeleteOnSubmit(provider);
                        }
                        db.SubmitChanges();

                        result = true;
                    }
                }
            }
            catch (Exception)
            {
            }
            return result;
        }

        #region Get Methods
        public ProviderDTO Get(System.Guid providerID)
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return ProviderMapper.ToBusinessObjectHasChildren(biocode.ProviderEntities.SingleOrDefault(a => a.ProviderID == providerID), typeof(ServiceProvider), typeof(ProviderContact));
            }
        }

        public ProviderDTO Get(string providerNumber)
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return ProviderMapper.ToBusinessObject(biocode.ProviderEntities.SingleOrDefault(a => a.ProviderNumber == providerNumber));
            }
        }

        public IList<ProviderEntity> GetAll()
        {
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                return biocode.ProviderEntities.Where(x => x.Active).ToList();
            }
        }
        #endregion

        #region Insert Methods
        public bool Insert(ProviderDTO entity)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                biocode.ProviderEntities.InsertOnSubmit(ProviderMapper.ToEntity(new ProviderEntity(), entity));
                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return result;
        }

        public bool Insert(ProviderDTO entity, string username)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                entity.ProviderID = Helper.NewSeqGuid();
                entity.ModifiedBy = username;
                entity.ModifiedDate = DateTime.Now;
                entity.Active = true;

                if (entity.ProviderContactCollection != null && entity.ProviderContactCollection.Count > 0)
                {
                    foreach (var item in entity.ProviderContactCollection)
                    {
                        var contact = ContactMapper.ToEntity(new Contact(), item.Contact);
                        contact.ContactID = Helper.NewSeqGuid();
                        biocode.Contacts.InsertOnSubmit(contact);

                        item.ProviderContactID = Helper.NewSeqGuid();
                        item.ContactID = contact.ContactID;
                        item.ProviderID = entity.ProviderID;

                        biocode.ProviderContacts.InsertOnSubmit(ProviderContactMapper.ToEntity(new ProviderContact(), item));
                    }
                }

                biocode.ProviderEntities.InsertOnSubmit(ProviderMapper.ToEntity(new ProviderEntity(), entity));
                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return result;
        }

        public bool Insert(IList<ProviderEntity> list)
        {
            bool result = false;
            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                biocode.ProviderEntities.InsertAllOnSubmit(list);
                try
                {
                    biocode.SubmitChanges();
                    result = true;
                }
                catch (ChangeConflictException)
                {
                    //  A possible concurrency exception occurred.  Let's see if
                    //  we can resolve it.
                    foreach (var conflict in biocode.ChangeConflicts)
                    {
                        conflict.Resolve(RefreshMode.KeepCurrentValues);
                    }
                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  It didn't work, so throw a new exception.
                        // throw new Exception("A concurrency error occurred!");
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
            }

            return result;
        }
        #endregion

        public bool Update(ProviderDTO dto)
        {
            bool result = false;

            using (OBSDataContext biocode = new OBSDataContext(base.connectionString))
            {
                var entity = biocode.ProviderEntities.SingleOrDefault(x => x.ProviderID == dto.ProviderID);
                if (entity != null)
                {
                    biocode.ProviderContacts.DeleteAllOnSubmit(entity.ProviderContacts);
                    biocode.Contacts.DeleteAllOnSubmit(entity.ProviderContacts.Select(x => x.Contact));

                    dto.Active = true;
                    entity = ProviderMapper.ToEntity(entity, dto);

                    foreach (var contact in dto.ProviderContactCollection.Where(x => !x.IsDeleted))
                    {
                        contact.ProviderContactID = Helper.NewSeqGuid();
                        contact.ProviderID = dto.ProviderID;
                        contact.ContactID = contact.Contact.ContactID;
                        var entityContact = ContactMapper.ToEntity(new Contact(), contact.Contact);
                        biocode.Contacts.InsertOnSubmit(entityContact);
                        entity.ProviderContacts.Add(ProviderContactMapper.ToEntity(new ProviderContact(), contact));
                    }

                    try
                    {
                        biocode.SubmitChanges();
                        result = true;
                    }
                    catch (ChangeConflictException)
                    {
                        //  A possible concurrency exception occurred.  Let's see if
                        //  we can resolve it.
                        foreach (var conflict in biocode.ChangeConflicts)
                        {
                            conflict.Resolve(RefreshMode.KeepCurrentValues);
                        }
                        try
                        {
                            biocode.SubmitChanges();
                            result = true;
                        }
                        catch (ChangeConflictException)
                        {
                            //  It didn't work, so throw a new exception.
                            // throw new Exception("A concurrency error occurred!");
                        }
                        catch (Exception)
                        {
                            // throw new Exception("There was an error saving this record!");
                        }
                    }
                    catch (Exception)
                    {
                        // throw new Exception("There was an error saving this record!");
                    }
                }
            }

            return result;
        }
    }
}
